package com.example.demo01.utils;

import com.example.demo01.entity.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class POIUtil {


    @Test
    public void testWriteByPOI() throws IOException {
        //0.工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        //1.工作表
        Sheet sheet = wb.createSheet();//不写名称默认是Sheet0
        wb.createSheet("工作表一");
        wb.createSheet("工作表二");
        //2.行
        Row row = sheet.createRow(0);
        //3.单元格
        Cell cell = row.createCell(0);
        //4.填充数据到单元格
        cell.setCellValue("学生信息表");


        //创建excel文件对象，用来装载poi写入的数据
        File file = new File("testPoi.xlsx");
        //将数据写到excel文件需要一个输出流
        FileOutputStream fos = new FileOutputStream(file);
        wb.write(fos);
        wb.close();

    }

    @Test
    public void testReadByPOI() throws IOException {
        //0.获取要读取的excel文件
        Workbook wb = new XSSFWorkbook("testPoi.xlsx");
        //1.获取工作表,根据索引获取第一个工作表
        Sheet sheet0 = wb.getSheetAt(0);
        Row row = sheet0.getRow(0);
        Cell cell = row.getCell(0);
        String stringCellValue = cell.getStringCellValue();
        System.out.println(stringCellValue);
    }

    /**
     * 实际工作中的导出excel,这里以导出学生信息表student.xlsx为例
     *
     * @throws Exception
     */
    public static void exportExcel(List<User> userList) throws Exception {
        //0.创建工作簿
        Workbook wb = new XSSFWorkbook();
        //1.创建工作表
        Sheet userSheet = wb.createSheet("用户信息表");
        //2.制作表头
        Row row_0 = userSheet.createRow(0);
        Cell cell_0_4 = row_0.createCell(0);
        cell_0_4.setCellValue("用户信息详情表");
        //2.0制作表题
        Row row_1 = userSheet.createRow(1);
        Cell cell_1_0 = row_1.createCell(0);
        cell_1_0.setCellValue("用户主键");
        Cell cell_1_1 = row_1.createCell(1);
        cell_1_1.setCellValue("用户姓名");
        Cell cell_1_2 = row_1.createCell(2);
        cell_1_2.setCellValue("用户角色");
        Cell cell_1_3 = row_1.createCell(3);
        cell_1_3.setCellValue("联系电话");
        Cell cell_1_4 = row_1.createCell(4);
        cell_1_4.setCellValue("家庭住址");
        //2.0.合并表头
        userSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        //2.1.实际表头也是一个单元格，我们还可以为单元格设置样式，比如颜色字体
        //2.1.0.先搞一个样式对象,样式由工作簿创建
        CellStyle cellStyle = wb.createCellStyle();
        //2.1.1.添加水平居中的样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //添加垂直居中的样式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //2.2.设置样式到具体单元格
        cell_0_4.setCellStyle(cellStyle);
        //3.导出数据
        for (int i = 0; i < userList.size(); i++) {
            //从第三行开始添加数据
            Row row = userSheet.createRow(i + 2);
            for (int j = 0; j < 5; j++) {
                Cell cell_0 = row.createCell(0);
                cell_0.setCellValue(userList.get(i).getUserId());
                Cell cell_1 = row.createCell(1);
                cell_1.setCellValue(userList.get(i).getName());
                Cell cell_2 = row.createCell(2);
                cell_2.setCellValue(userList.get(i).getRoleName());
                Cell cell_3 = row.createCell(3);
                cell_3.setCellValue(userList.get(i).getPhone());
                Cell cell_4 = row.createCell(4);
                cell_4.setCellValue(userList.get(i).getHomeAddr());
            }

        }

        //创建excel文件对象，用来装载poi写入的数据
        File file = new File("user.xlsx");
        //将数据写到excel文件需要一个输出流
        FileOutputStream fos = new FileOutputStream(file);
        wb.write(fos);
        wb.close();
    }
}
